<?php
class Metro_model extends CI_Model {

	private $_software_version_table = 'software_version';
	private $_software_download_table = 'software_download';
	private $_options_table = 'options';
	private $_user_table = 'user';

	public function __construct() {
        parent::__construct();
	}

	public function _rander_filter_options($table='version') {
		if ($table == 'version') {
			$query_fields = 'device, supplier, area, type';
			$query_table = $this->_software_version_table;
		} elseif ($table == 'download') {
			$query_fields = 'line, range, center, device, type';
			$query_table = $this->_software_download_table;
		}
		$this->load->database();
		$this->db->select($query_fields);
		$this->db->from($query_table);
		return $this->db->get()->result_array();
	}

	public function _get_software_version_data($query) {
		$this->load->database();
		if (isset($query['fromdate']) || isset($query['todate'])) {
			$this->db->where("downloadtime2 BETWEEN format('" . $query['fromdate'] . "','yyyy-mm-dd') AND format('" . $query['todate'] . "','yyyy-mm-dd')", NULL, FALSE);
			unset($query['fromdate']);
			unset($query['todate']);
		}
		$this->db->select('ID, supplier, device, area, type, finish, beforevision, aftervision');
		$this->db->from($this->_software_version_table);
		$this->db->where($query);

		return $this->db->get()->result_array();
	}

	public function _get_software_version_item($query) {
		$this->load->database();
		$this->db->from($this->_software_version_table);
		$this->db->where($query);

		return $this->db->get()->result_array();
	}

	public function _add_software_version_data($data) {
		$this->load->database();
		return $this->db->insert($this->_software_version_table, $data);
	}

	public function _update_software_version_data($query) {
		$this->load->database();
		$this->db->where('ID', (int)$query['id']);
		unset($query['id']);
		return $this->db->update($this->_software_version_table, $query);
	}

	public function _delete_software_version_item($query) {
		$this->load->database();
		$this->db->where($query);
		return $this->db->delete($this->_software_version_table);
	}

	public function _get_software_download_data($query) {
		$this->load->database();
		$this->db->select('ID, noticetime, line, range, supplier, device, area, type, aftervision, back');
		$this->db->from($this->_software_download_table);
		if (isset($query['fromdate']) || isset($query['todate'])) {
			$this->db->where("downloadtime BETWEEN format('" . $query['fromdate'] . "','yyyy-mm-dd') AND format('" . $query['todate'] . "','yyyy-mm-dd')", NULL, FALSE);
			unset($query['fromdate']);
			unset($query['todate']);
		}
		$this->db->where($query);

		return $this->db->get()->result_array();
	}

	public function _get_software_download_item($query) {
		$this->load->database();
		$this->db->from($this->_software_download_table);
		$this->db->where($query);

		return $this->db->get()->result_array();
	}

	public function _add_software_download_data($data) {
		$this->load->database();
		return $this->db->insert($this->_software_download_table, $data);
	}

	public function _update_software_download_data($query) {
		$this->load->database();
		$this->db->where('ID', (int)$query['id']);
		unset($query['id']);
		return $this->db->update($this->_software_download_table, $query);
	}

	public function _delete_software_download_item($query) {
		$this->load->database();
		$this->db->where($query);
		return $this->db->delete($this->_software_download_table);
	}

	public function _get_options() {
		$this->load->database();
		$this->db->from($this->_options_table);
		return $this->db->get()->result_array();
	}

	public function _check_login($query) {
		$this->load->database();
		$this->db->from($this->_user_table);
		$this->db->where($query);
		$result = $this->db->get()->result_array();
		return count($result);
	}

	public function _get_download_finished_data($query) {
		$where = 'area+device+type+aftervision IN(SELECT area+device+type+MAX(aftervision) FROM ' . $this->_software_version_table . ' WHERE finish=1 GROUP BY area, device, type)';
		$this->load->database();
		$this->db->select('area, device, type, aftervision, downloadtime2, supplier');
		$this->db->from($this->_software_version_table);
		$this->db->where($where);
		$this->db->where($query);
		return $this->db->get()->result_array();
	}

	public function _get_download_unfinished_data($query) {
		$this->load->database();
		$this->db->select('center,line,station,range,supplier,area,device,type,aftervision,downloadtime');
		$this->db->from($this->_software_download_table);
		$this->db->where('area+device+type+aftervision IN 
			(
			SELECT area+device+type+aftervision FROM ' . $this->_software_version_table . ' 
			WHERE area+device+type+aftervision IN 
				(
				SELECT area+device+type+max(aftervision) FROM ' . $this->_software_version_table . ' WHERE finish=0
				GROUP BY area,device,type
				)
			)'
		);
		$this->db->where('line+area+device+type+aftervision+Cstr(downloadtime) IN 
			(
			SELECT line+area+device+type+aftervision+Cstr(max(downloadtime)) FROM ' . $this->_software_download_table . ' 
			GROUP BY line+area+device+type+aftervision
			)'
		);
		$this->db->where($query);
		return $this->db->get()->result_array();
	}

	public function _get_version_download_time($query) {
		$where = 'area+device+type+aftervision IN(SELECT area+device+type+aftervision FROM ' . $this->_software_version_table . ' WHERE finish=1)';
		$this->load->database();
		$this->db->select("center, area, device, type, aftervision, DateDiff('d', min(noticetime), max(downloadtime)) AS downloaddays");
		$this->db->from($this->_software_download_table);
		$this->db->group_by(array('center', 'area', 'device', 'type', 'aftervision'));
		$this->db->where($where);
		if (isset($query['fromdate']) || isset($query['todate'])) {
			$this->db->where("downloadtime BETWEEN format('" . $query['fromdate'] . "','yyyy-mm-dd') AND format('" . $query['todate'] . "','yyyy-mm-dd')", NULL, FALSE);
			unset($query['fromdate']);
			unset($query['todate']);
		}
		$this->db->where($query);
		return $this->db->get()->result_array();
	}

	public function _get_back_data($query) {
		$this->load->database();
		$this->db->select('supplier, device, type, COUNT(aftervision), FORMAT(SUM(finish)), FORMAT(SUM(back))');
		$this->db->from($this->_software_version_table);
		if (isset($query['fromdate']) || isset($query['todate'])) {
			$this->db->where("downloadtime2 BETWEEN format('" . $query['fromdate'] . "','yyyy-mm-dd') AND format('" . $query['todate'] . "','yyyy-mm-dd')", NULL, FALSE);
			unset($query['fromdate']);
			unset($query['todate']);
		}
		$this->db->where($query);
		$this->db->group_by('supplier, device, type');
		return $this->db->get()->result_array();
	}

	public function _get_back_detail($query) {
		$this->load->database();
		$this->db->select('supplier, area, device, type, softname, beforevision, aftervision, downloadtime1, downloadtime2, DateDiff(\'d\', downloadtime1, downloadtime2) AS downloadtime, finish, back, explain, responsible');
		if (isset($query['fromdate']) || isset($query['todate'])) {
			$this->db->where("downloadtime2 BETWEEN format('" . $query['fromdate'] . "','yyyy-mm-dd') AND format('" . $query['todate'] . "','yyyy-mm-dd')", NULL, FALSE);
			unset($query['fromdate']);
			unset($query['todate']);
		}
		$this->db->where($query);
		return $this->db->get($this->_software_version_table)->result_array();
	}

}
